﻿/*
Created		31/03/2012
Modified		11/05/2012
Project		
Model			
Company		
Author		
Version		
Database		MS SQL 2005 
*/
Create Database QLKS2
go 
use QLKS2
go


Create table [RENTERS]
(
	[CustomerID] Integer Identity(100,1) NOT NULL,
	[Name] Nvarchar(20) NULL,
	[Sex] Bit NULL,
	[PersonID] Nvarchar(12) NOT NULL, UNIQUE ([PersonID]),
	[Birthday] Smalldatetime NULL,
	[Place_of_issue] Nvarchar(35) NULL,
	[Date_of_issue] Smalldatetime NULL,
	[Addr] Nvarchar(30) NULL,
	[districts] Nvarchar(20) NULL,
	[provinces] Nvarchar(20) NULL,
	[Country] Nvarchar(20) NULL,
	[HomePhone] Numeric(15,0) NULL,
	[Fax] Char(30) NULL,
	[Mobile] Numeric(15,0) NULL,
	[Email] Nvarchar(30) NULL,
Primary Key ([CustomerID])
) 
go

Create table [ROOM]
(
	[RoomID] Nvarchar(10) NOT NULL,
	[RoomTypeID] Nvarchar(10) NOT NULL,
	[RoomName] Nvarchar(20) NULL,
	[NumberCustomer] Integer NULL,
	[TinhTrang] Bit NULL,
	[Notes] Nvarchar(250) NULL,
Primary Key ([RoomID])
) 
go

Create table [ROOMTYPE]
(
	[RoomTypeID] Nvarchar(10) NOT NULL,
	[TypeName] Nvarchar(20) NOT NULL,
	[Cost] Numeric(8,0) NOT NULL,
	[Descript] Text NULL,
Primary Key ([RoomTypeID])
) 
go

Create table [RENTFORM_DETAIL]
(
	[CheckInID] Integer Identity(1000,1) NOT NULL,
	[EmployeeID] Nvarchar(10) NOT NULL,
	[Deposit] Numeric(10,0) NOT NULL,
	[CustomerID] Integer NOT NULL,
	[DetailsID] Char(10) NOT NULL,
Primary Key ([CheckInID])
) 
go

Create table [EMPLOYEE]
(
	[EmployeeID] Nvarchar(10) NOT NULL,
	[Name] Nvarchar(20) NULL,
	[Sex] Bit NULL,
	[BirthDay] Smalldatetime NULL,
	[PersonID] Nvarchar(15) NULL, UNIQUE ([PersonID]),
	[DateOfIssue] Datetime NULL,
	[PlaceOfIssue] Nvarchar(50) NULL,
	[Hometown] Nvarchar(40) NULL,
	[Email] Nvarchar(30) NULL,
	[Mobile] Numeric(15,0) NULL,
	[Addr] Nvarchar(40) NULL,
	[Salary] Numeric(10,0) NULL,
	[Images] Image NULL,
	[HomePhone] Numeric(15,0) NULL,
Primary Key ([EmployeeID])
) 
go

Create table [ACCOUNT]
(
	[UserName] Nvarchar(30) NOT NULL,
	[Pass] Nvarchar(30) NOT NULL,
	[EmployeeID] Nvarchar(10) NOT NULL,
	[IDType] Nvarchar(10) NOT NULL,
Primary Key ([User],[EmployeeID])
) 
go

Create table [ACCTYPE]
(
	[IDType] Nvarchar(10) NOT NULL,
	[NameType] Nvarchar(20) NULL,
	[Note] Text NULL,
Primary Key ([IDType])
) 
go

Create table [BILL]
(
	[Bill_ID] Nvarchar(10) NOT NULL,
	[CheckInID] Integer NOT NULL,
	[Day] Smalldatetime NULL,
	[Cost] Numeric(10,0) NULL,
	[PayType] Nvarchar(20) NULL,
Primary Key ([Bill_ID],[CheckInID])
) 
go

Create table [ROOMSERVICES]
(
	[ServicesID] Nvarchar(10) NOT NULL,
	[Name] Nvarchar(20) NULL,
	[Cost] Numeric(8,0) NOT NULL,
Primary Key ([ServicesID])
) 
go

Create table [BILL_DETAIL]
(
	[Bill_ID] Nvarchar(10) NOT NULL,
	[CheckInID] Integer NOT NULL,
	[ServicesID] Nvarchar(10) NOT NULL,
	[AmountServices] Integer NULL,
Primary Key ([Bill_ID],[CheckInID],[ServicesID])
) 
go

Create table [ROOM_DETAILS]
(
	[DetailsID] Char(10) NOT NULL,
	[RoomID] Nvarchar(10) NOT NULL,
	[AmountRoom] Integer NULL,
	[CheckInDay] Datetime NULL,
	[CheckOutDay] Datetime NULL,
	[NguoiLon] Integer NULL,
	[TreEm] Integer NULL,
Primary Key ([DetailsID])
) 
go


Alter table [RENTFORM_DETAIL] add  foreign key([CustomerID]) references [RENTERS] ([CustomerID])  on update no action on delete no action 
go
Alter table [ROOM_DETAILS] add  foreign key([RoomID]) references [ROOM] ([RoomID])  on update no action on delete no action 
go
Alter table [ROOM] add  foreign key([RoomTypeID]) references [ROOMTYPE] ([RoomTypeID])  on update no action on delete no action 
go
Alter table [BILL] add  foreign key([CheckInID]) references [RENTFORM_DETAIL] ([CheckInID])  on update no action on delete no action 
go
Alter table [ACCOUNT] add  foreign key([EmployeeID]) references [EMPLOYEE] ([EmployeeID])  on update no action on delete no action 
go
Alter table [RENTFORM_DETAIL] add  foreign key([EmployeeID]) references [EMPLOYEE] ([EmployeeID])  on update no action on delete no action 
go
Alter table [ACCOUNT] add  foreign key([IDType]) references [ACCTYPE] ([IDType])  on update no action on delete no action 
go
Alter table [BILL_DETAIL] add  foreign key([Bill_ID],[CheckInID]) references [BILL] ([Bill_ID],[CheckInID])  on update no action on delete no action 
go
Alter table [BILL_DETAIL] add  foreign key([ServicesID]) references [ROOMSERVICES] ([ServicesID])  on update no action on delete no action 
go
Alter table [RENTFORM_DETAIL] add  foreign key([DetailsID]) references [ROOM_DETAILS] ([DetailsID])  on update no action on delete no action 
go


Set quoted_identifier on
go


Set quoted_identifier off
go


/* Roles permissions */


/* Users permissions */

insert into ACCTYPE(IDType,NameType,Note) values ('Admin','Admin',N'Developer')
insert into ACCTYPE(IDType,NameType,Note) values ('Manager','Manager',N'Quản lí')
insert into ACCTYPE(IDType,NameType,Note) values ('Employee','Employe',N'Nhân Viên')
insert into ACCTYPE(IDType,NameType,Note) values ('Boss','Boss',N'Giám đốc')
---------------
drop table employee
set dateformat dmy
insert into EMPLOYEE
	values('em01',N'tùng',1,'12/8/2005','230789456','12/12/2012','gia lai',N'Gia Lai','tung@tung.com','98761346',N'd2-binh thanh',7000000,'','654654364')
insert into EMPLOYEE
	values('de01',N'Lê Đức Toàn',1,'28/12/1991','215167584','12/12/2012',N'Bình Dương','Binh Duong','toanit@leductoan.com','1665552073',N'Bình Thạnh',10000000,'','0951020277')	
insert into ACCTYPE
	values ('01','admin','VIP')
	
update EMPLOYEE set images ='' where EmployeeID = 'em01'

insert into ACCOUNT(UserName,Pass,EmployeeID,IDType)
	values ('admin','admin','de01','Admin')
insert into ACCOUNT(UserName,Pass,EmployeeID,IDType)
	values ('tung','123456','de01','Boss')
-------------
Insert into ROOMTYPE(RoomTypeID,TypeName,Cost,Descript)
values ('01','Làng Mộc','350000','Làng cổ')
Insert into ROOMTYPE(RoomTypeID,TypeName,Cost,Descript)
values ('02','Làng Ven Sông','600000','Làng Nhìn ra sông')
Insert into ROOMTYPE(RoomTypeID,TypeName,Cost,Descript)
values ('03','Làng Tây Nguyên','350000','Nhà sàn 2 phòng')
-----------------------------
insert into ROOM(RoomID,RoomTypeID,RoomName,NumberCustomer,TinhTrang,Notes)
	values ('01','01','Phòng 3','3','False','null')
insert into ROOM(RoomID,RoomTypeID,RoomName,NumberCustomer,TinhTrang,Notes)
	values ('02','02','Phòng 4','2','True','null')
insert into ROOM(RoomID,RoomTypeID,RoomName,NumberCustomer,TinhTrang,Notes)
	values ('03','02','Phòng 5','3','False','null')
insert into ROOM(RoomID,RoomTypeID,RoomName,NumberCustomer,TinhTrang,Notes)
	values ('04','03','Phòng 6','4','True','null')
	
----------------------------------------------------------
-----------Đăng nhập-------------
create proc sp_check_login
(
	@id char(30),
	@pass char (30)
)
as
	begin
	select IDType from ACCOUNT where username = @id and pass = @pass
	end
exec sp_check_login 'admin','admin'
-----------------------
create proc sp_CheckQuyen_QuyenHan
(
	@id char(30),
	@pass char(30)
)
as
	begin
		select NameType 
		from ACCTYPE t, ACCOUNT c
		where t.IDType =c.IDType and username =@id and pass=@pass
	end
exec sp_CheckQuyen_QuyenHan 'admin','admin'

-----------------------
create proc sp_Check_InfoEmployee
(
	@id nvarchar(30),
	@pass nvarchar(30)
)
as
	begin
		select e.NAME, e.Addr
		from EMPLOYEE e, ACCOUNT c
		where e.EmployeeID =c.EmployeeID and username =@id and pass=@pass
	end
exec sp_Check_InfoEmployee 'admin','admin'
----------DOI PASS-----------------
create Procedure sp_UpdatePass
(
    @ID nvarchar(30), 
    @pass nvarchar(30) 
)
as
	begin   
		update Account set pass = @pass where username like @ID
    end    
exec sp_UpdatePass 'admin','admin'
-------------------------
---THÊM KIỂU PHÒNG------------
create proc sp_insert_RoomType
(
	@id nvarchar(10),
	@type nvarchar(20),
	@cost numeric(8,0),
	@descr text
)
as
	begin
		insert into ROOMTYPE(RoomTypeID,TypeName,Cost,Descript)
			values (@id,@type,@cost,@descr)
	end
exec sp_insert_RoomType '4','Phòng ốc','1000','nga gi'
select * from ROOMTYPE

---------Sửa kiểu Phòng----------------
create proc sp_Update_Roomtype (
	@id nvarchar(10),
	@type nvarchar(20),
	@cost numeric(8,0),
	@descr text
)
as
	update ROOMTYPE set TypeName = @type, Cost = @cost,Descript = @descr
	where RoomTypeID = @id
	

---------------Thêm Nhân Viên-----------
create proc sp_Insert_Employee
(
	@EmployeeID nvarchar(10),
	@Name nvarchar(30),
	@Sex bit,
	@BirthDay smalldatetime,
	@PersonID char(15),
	@DateOfIssue datetime,
	@PlaceOfIssue nvarchar(430),
	@Hometown nvarchar(40),
	@Email nvarchar(30),
	@Mobile char(15),
	@AddressE nvarchar(40),
	@Salary char(10),
	@Images image,
	@HomePhone char(15)
)
as
	begin
		insert into EMPLOYEE(EmployeeID,Name,Sex,BirthDay, PersonID, DateOfIssue, PlaceOfIssue, Hometown, Email, Mobile, Addr, Salary, Images, HomePhone)
		  values (@EmployeeID,@Name,@Sex,@BirthDay,@PersonID,@DateOfIssue,@PlaceOfIssue,@Hometown,@Email,@Mobile,@AddressE,@Salary,@Images,@HomePhone)
	end

exec sp_Insert_Employee 'dssse01',N'Lê Đức cớp',1,'28/12/1991','21dfdv51674','12/12/2012',N'Bình Dương','Binh Duong','toanit@leductoan.com','65552073',N'Bình Thạnh',10000000,'','0951020277'
set dateformat dmy

-------Select List Employee---------

create proc sp_Select_Employee
as
	select EmployeeID,Name,Sex,BirthDay, Mobile, Addr, Email
		from EMPLOYEE
		
exec sp_Select_Employee 

---Tìm kiếm Nhân Viên-----
create proc sp_FindbyName_Employee 
(@name nvarchar(30))
as
	Select EmployeeID,Name,Sex,BirthDay, Mobile, Addr, Email 
	from EMPLOYEE where Name like ('%'+@name+'%')
	
EXEC sp_FindbyName_Employee 'tùng'

------select 1 Nhân viên-----
create proc sp_SelectOne_Employee
(@id nvarchar(10))
as
	select * from EMPLOYEE 
		where EmployeeID like @id

exec sp_SelectOne_Employee 'de01'

-----Xóa nhân viên---------
create proc sp_Delete_eployee
(@id nvarchar(20) )
as
	begin
		DELETE FROM ACCOUNT
			where EmployeeID like @id
		DELETE FROM EMPLOYEE
			where EmployeeID like @id
	end
exec sp_Delete_eployee 'de01'
select *from EMPLOYEE
	
------Sửa nhân viên---------
create proc sp_Update_employee
(
	@EmployeeID nvarchar(10),
	@Name nvarchar(30),
	@Sex bit,
	@BirthDay smalldatetime,
	@PersonID char(15),
	@DateOfIssue datetime,
	@PlaceOfIssue nvarchar(430),
	@Hometown nvarchar(40),
	@Email nvarchar(30),
	@Mobile char(15),
	@AddressE nvarchar(40),
	@Salary char(10),
	@Images image,
	@HomePhone char(15)
)
as
	begin
		UPDATE EMPLOYEE SET Name = @Name, Sex = @Sex, BirthDay = @BirthDay, PersonID = @PersonID,
		 DateOfIssue = @DateOfIssue, PlaceOfIssue = @PlaceOfIssue, Hometown = @Hometown, Email = @Email,
		  Mobile = @Mobile, Addr = @AddressE, Salary = @Salary, Images = @Images, HomePhone = @HomePhone
		WHERE EmployeeID = @EmployeeID
	end
exec sp_Update_employee 'em01',N'HUTECH',1,'26/12/1991','2306164625','30/4/1975',N'Gia Lai','Hải Phòng','teo@leductoan.com','65552073',N'Pleiku',9999999,'','0951020277'
select *from EMPLOYEE
--------
-------------danh sách Nguoi dung----------------
alter proc sp_SelectAll_Acc
as
	SELECT A.EmployeeID, EM.Name, UserName, A.IDType 
	FROM ACCOUNT A, EMPLOYEE EM
	WHERE A.EmployeeID=EM.EmployeeID
	
sp_SelectAll_Acc
----------------Xóa kiểu phòng--------
create proc sp_delete_RoomType
(@id nvarchar(20))
as
begin
	delete from ROOMTYPE where RoomTypeID = @id
end
exec sp_delete_RoomType '01'
select *from ROOMTYPE
--------------danh sách kiểu phòng---------
create proc sp_ListAll_RoomType
as
	select * from RoomType

exec sp_ListAll_RoomType
-----------------------------
create proc sp_insert_room
(
	@id nvarchar(10),
	@roomtypeID nvarchar(10),
	@name nvarchar(20),
	@num int,
	@tinhtrang bit,
	@Note nvarchar(250)
)
as
	insert into ROOM(RoomID,RoomTypeID,RoomName,NumberCustomer,TinhTrang,Notes)
	values (@id,@roomtypeID,@name,@num,@tinhtrang,@Note)
exec sp_insert_room '7','03','Phòng 7','4','False','Phong dat tieu chuan'
-------------danh sách phòng----------------
create proc sp_SelectAll_Room
as
	select RoomID, RoomName, TypeName, NumberCustomer, [RoomStatus] = case TinhTrang when '0' then N'Còn trống'
	when '1' then N'Đã Thuê ' end, Notes
	 from ROOM r, ROOMTYPE rt
	 where r.RoomTypeID=rt.RoomTypeID 
sp_SelectAll_Room
------------------Sửa phòng---------------------------
create proc sp_Update_room
(
	@id nvarchar(10),
	@roomtypeID nvarchar(10),
	@name nvarchar(20),
	@num int,
	@tinhtrang bit,
	@Note nvarchar(250)
)
as
	update ROOM set RoomTypeID = @roomtypeID, RoomName=@name, NumberCustomer=@num,TinhTrang=@tinhtrang, Notes = @Note 
	where RoomID =@id
exec sp_Update_room '7','03','Phòng 7','4','False','Phong du tieu chuan'
----------------------Xóa phòng--------------------
create proc sp_delete_room
(@id nvarchar(10))
as
	DELETE FROM ROOM
			where RoomID like @id

sp_delete_room '7'
------------------Thêm khách hàng--------------------------
create proc sp_Insert_Renter
(
	@name nvarchar(20),
	@sex bit,
	@cmnd nvarchar(12),
	@noicap nvarchar(35),
	@ngaycap smalldatetime,
	@diachi nvarchar(30),
	@quanhuyen nvarchar(30),
	@tinh nvarchar(30),
	@quocgia nvarchar(30),
	@dienthoai char(15),
	@fax char(30),
	@email nvarchar(30),
	@didong char(15)	
)
as
begin
	insert into RENTERS(Name,Sex,PersonID,Place_of_issue,Date_of_issue,Addr,districts,provinces,Country,HomePhone,Fax,Email,Mobile)
	values (@name,@sex,@cmnd,@noicap,@ngaycap,@diachi,@quanhuyen,@tinh,@quocgia,@dienthoai,@fax,@email,@didong)
end
exec sp_Insert_Renter '1','true','23','1','25/5/2005','1','chupah','gia lai','vietnam','2342','66666','dfg@dg','0196461'

insert into RENTERS(Name,Sex,PersonID,Place_of_issue,Date_of_issue,Addr,districts,provinces,Country,HomePhone,Fax,Email,Mobile)
	values ('1','true','23','1','25/5/2005','1','chupah','gia lai','vietnam','2342','66666','dfg@dg','0196461')
	
	-------------Lấy mã tự tăng mới nhập vào---------------------
create proc sp_get_rentersIDnew
as
	Select @@IDENTITY as [IDNewest]
	
---------------------------------------------------------------------
---------Sửa kiểu Phòng----------------
create proc sp_Update_Roomtype (
	@id nvarchar(10),
	@type nvarchar(20),
	@cost numeric(8,0),
	@descr nvarchar(255)
)
as
	update ROOMTYPE set TypeName = @type, Cost = @cost,Descript = @descr
	where RoomTypeID = @id
	
------------------------------------------------------------
select r.RoomID, r.RoomName,
	(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE 1 BETWEEN DAY(CheckInDay) and DAY(CheckOutDay) and 5 BETWEEN month(CheckInDay) and month(CheckOutDay) and 2012 BETWEEN year(CheckInDay) and year(CheckOutDay))) THEN '1' ELSE '0' END) AS [01]
	from dbo.ROOM_DETAILS d,ROOM r
--where d.RoomID = R.RoomID  
GROUP BY r.RoomID,r.RoomName
--------------------------------------------
/****** Object:  StoredProcedure [dbo].[sp_ThongKePhong]    Script Date: 06/02/2012 23:12:28 ******/
create proc [dbo].[sp_ThongKePhong31]
(@month int, @year int, @roomTypeid nvarchar(10))
as
	begin
	select r.RoomID, r.RoomName,
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '1'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [01],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '2'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [02],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '3'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [03],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '4'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [04],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '5'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [05],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '6'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [06],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '7'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [07],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '8'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [08],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '9'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [09],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '10'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [10],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '11'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [11],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '12'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [12],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '13'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [13],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '14'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [14],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '15'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [15],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '16'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [16],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '17'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [17],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '18'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [18],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '19'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [19],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '20'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [20],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '21'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [21],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '22'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [22],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '23'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [23],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '24'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [24],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '25'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [25],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '26'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [26],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '27'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [27],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '28'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [28],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '29'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [29],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '30'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [30],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '31'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [31]	
	from ROOM_DETAILS d,ROOM r
	where r.RoomTypeID = @roomTypeid
	GROUP BY r.RoomID,r.RoomName	
end
--loại 30,28,29 ngày tương tự
create proc [dbo].[sp_ThongKePhong30]
(@month int, @year int, @roomTypeid nvarchar(10))
as
	begin
	select r.RoomID, r.RoomName,
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '1'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [01],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '2'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [02],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '3'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [03],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '4'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [04],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '5'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [05],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '6'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [06],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '7'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [07],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '8'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [08],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '9'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [09],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '10'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [10],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '11'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [11],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '12'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [12],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '13'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [13],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '14'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [14],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '15'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [15],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '16'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [16],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '17'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [17],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '18'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [18],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '19'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [19],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '20'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [20],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '21'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [21],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '22'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [22],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '23'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [23],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '24'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [24],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '25'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [25],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '26'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [26],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '27'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [27],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '28'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [28],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '29'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [29],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '30'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [30]
	from ROOM_DETAILS d,ROOM r
	where r.RoomTypeID = @roomTypeid
	GROUP BY r.RoomID,r.RoomName	
end
---------------------------------------------------
create proc [dbo].[sp_ThongKePhong29]
(@month int, @year int, @roomTypeid nvarchar(10))
as
	begin
	select r.RoomID, r.RoomName,
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '1'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [01],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '2'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [02],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '3'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [03],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '4'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [04],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '5'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [05],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '6'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [06],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '7'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [07],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '8'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [08],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '9'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [09],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '10'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [10],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '11'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [11],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '12'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [12],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '13'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [13],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '14'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [14],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '15'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [15],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '16'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [16],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '17'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [17],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '18'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [18],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '19'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [19],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '20'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [20],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '21'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [21],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '22'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [22],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '23'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [23],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '24'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [24],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '25'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [25],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '26'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [26],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '27'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [27],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '28'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [28],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '29'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [29]
	from ROOM_DETAILS d,ROOM r
	where r.RoomTypeID = @roomTypeid
	GROUP BY r.RoomID,r.RoomName	
end
-------------------------------------------------------------
create proc [dbo].[sp_ThongKePhong28]
(@month int, @year int, @roomTypeid nvarchar(10))
as
	begin
	select r.RoomID, r.RoomName,
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '1'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [01],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '2'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [02],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '3'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [03],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '4'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [04],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '5'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [05],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '6'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [06],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '7'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [07],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '8'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [08],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '9'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [09],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '10'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [10],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '11'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [11],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '12'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [12],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '13'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [13],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '14'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [14],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '15'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [15],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '16'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [16],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '17'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [17],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '18'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [18],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '19'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [19],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '20'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [20],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '21'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [21],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '22'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [22],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '23'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [23],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '24'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [24],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '25'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [25],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '26'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [26],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '27'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [27],
		(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, '28'+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [28]
	from ROOM_DETAILS d,ROOM r
	where r.RoomTypeID = @roomTypeid
	GROUP BY r.RoomID,r.RoomName	
end
--------------------------------------------------------------------



exec sp_ThongKePhong '6','2012','02'
---------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_ThongKeTungPhong]
(@day int,@month int, @year int)
as
	select r.RoomID, r.RoomName,
	(CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, CONVERT(nvarchar(2),@day)+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END) AS [TRANG THAI]
	from ROOM_DETAILS d,ROOM r, ROOMTYPE rt
	group by  r.RoomID,r.RoomName
	order by r.RoomID

exec sp_ThongKeTungPhong '30','6','2012'
------------------------------------------------------------------------------------------------------------------------

create proc sp_add_checkIn
(
	@id char(10),
	@roomid nvarchar(10),
	@slphong int,
	@checkInday datetime,
	@checkOutday datetime,
	@slNguoilon int,
	@slTreem int
)
as
	begin
		insert into ROOM_DETAILS values (@id, @roomid, @slphong, @checkInday, @checkOutday, @slNguoilon, @slTreem)
	end
exec sp_add_checkIn '037','31','1','25/5/2012','2/6/2012','2','2'


create proc sp_add_rentform
(
	@EmployeeID nvarchar(10),
	@deposit char(10),
	@customerID int,
	@detailsID char (10)
)
as	
	begin
		insert into RENTFORM_DETAIL values (@EmployeeID, @deposit, @customerID, @detailsID)
	end
exec sp_add_rentform 'ad01','1000000','103','001'

select * from dbo.RENTFORM_DETAIL rd, dbo.RENTERS rt, dbo.EMPLOYEE e, dbo.ROOM_DETAILS r
where e.EmployeeID = rd.EmployeeID and rt.CustomerID = rd.CustomerID and r.DetailsID = rd.DetailsID
group by 

----------------------------------
create proc sp_Select_InfoRentForm (@maHD char(10))
as
	select rd.DetailsID as [Mã HĐ], rt.CustomerID as[Mã KH],rt.Name as[Tên Khách Hàng], PersonID as[CMND], r.RoomName as[Tên phòng], CheckInDay as[Ngày đặt], CheckOutDay as[Ngày trả], Deposit as [Tổng giá]
	from ROOM_DETAILS rd, RENTERS rt, RENTFORM_DETAIL rf, ROOM r
	where rd.DetailsID = rf.DetailsID and rt.CustomerID = rf.CustomerID and r.RoomID = rd.RoomID and rd.DetailsID=@maHD

exec sp_Select_InfoRentForm '001'
-----------------hÓA ĐƠN-----------------------
USE [QLKS3]
GO
/****** Object:  StoredProcedure [dbo].[sp_Select_InfoRentForm]    Script Date: 06/07/2012 21:29:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_Select_InfoRentForm] (@maHD char(10))
as
	select rd.DetailsID as [Mã HĐ], rt.CustomerID as[Mã KH],rt.Name as[Tên Khách Hàng], rt.Addr + ' - '+rt.districts + ' - '+ rt.provinces as[Địa chỉ], r.RoomName as[Tên phòng], CONVERT(VARCHAR(10), CheckInDay, 103) as[Ngày đặt], CONVERT(VARCHAR(10), CheckOutDay, 103) as[Ngày trả], SumMoney as [Đơn giá], SumMoney*10/100 as [VAT], SumMoney + SumMoney*10/100 as [Tổng tiền]
	from ROOM_DETAILS rd, RENTERS rt, RENTFORM_DETAIL rf, ROOM r
	where rd.DetailsID = rf.DetailsID and rt.CustomerID = rf.CustomerID and r.RoomID = rd.RoomID and rd.DetailsID=@maHD
	--------------------------------
USE [QLKS3]
GO
/****** Object:  StoredProcedure [dbo].[sp_Select_InfoCustomer]    Script Date: 06/07/2012 21:29:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_Select_InfoCustomer]
as
	select rd.DetailsID as [Mã HĐ], rt.CustomerID as[Mã KH],rt.Name as[Tên Khách Hàng], rt.Addr + ' - '+rt.districts + ' - '+ rt.provinces as[Địa chỉ], r.RoomName as[Tên phòng], CONVERT(VARCHAR(10), CheckInDay, 103) as[Ngày đặt], CONVERT(VARCHAR(10), CheckOutDay, 103) as[Ngày trả], Deposit as [Đơn giá]
	from ROOM_DETAILS rd, RENTERS rt, RENTFORM_DETAIL rf, ROOM r
	where rd.DetailsID = rf.DetailsID and rt.CustomerID = rf.CustomerID and r.RoomID = rd.RoomID
------------------Thông tin hóa đơn----------------------
alter proc sp_Select_InfoCustomer
as
	select rd.DetailsID as [Mã HĐ], rt.CustomerID as[Mã KH],rt.Name as[Tên Khách Hàng], PersonID as[CMND], r.RoomName as[Tên phòng], CheckInDay as[Ngày đặt], CheckOutDay as[Ngày trả],Deposit as [Đặt Cọc] ,SumMoney as [Tổng giá]
	from ROOM_DETAILS rd, RENTERS rt, RENTFORM_DETAIL rf, ROOM r
	where rd.DetailsID = rf.DetailsID and rt.CustomerID = rf.CustomerID and r.RoomID = rd.RoomID

exec sp_Select_InfoCustomer
-----------------
---Cap Nhat Tien/ Hàm này gọi sau khi Form khách và form setup phòng nhap xong---
select * from RENTFORM_DETAIL
CREATE PROC sp_update_summoney
AS
BEGIN
UPDATE RENTFORM_DETAIL
SET SumMoney = 
(rtype.Cost*rd.AmountRoom) 
	from ROOMTYPE rtype, ROOM r, ROOM_DETAILS rd, RENTERS rt, RENTFORM_DETAIL rf
	where rtype.RoomTypeID=r.RoomTypeID and r.RoomID=rd.RoomID and rf.DetailsID=rd.DetailsID and rt.CustomerID = rf.CustomerID and r.RoomID = rd.RoomID

END
EXEC sp_update_summoney
--------------------------Báo cáo doanh thu theo ngày---------------------------
create proc sp_ThongkeDoanhThuNgay
(
	@day int,@month int, @year int
)
as
	select r.RoomName,CONVERT(VARCHAR(10), CheckInDay, 103) as[Ngày đặt], CONVERT(VARCHAR(10), CheckOutDay, 103) as[Ngày trả], SUM(rf.Amount) as[Tổng tiền]
	from ROOM r, ROOM_DETAILS rd, RENTFORM_DETAIL rf
	where R.RoomID =rd.RoomID and rd.DetailsID = rf.DetailsID and CONVERT(DATETIME, CONVERT(nvarchar(2),@day)+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) LIKE CheckOutDay
	GROUP BY r.RoomName, rf.Amount, CheckOutDay, CheckInDay
	ORDER BY R.RoomName	
EXEC sp_ThongkeDoanhThuNgay '1','5','2012'

----------------------------Thống kê doanh thu theo tháng-----------------------------
create proc sp_ThongkeDoanhThuThang
(
	@month int, @year int
)
as
	select r.RoomName, CONVERT(VARCHAR(10), CheckInDay, 103) as[Ngày đặt], CONVERT(VARCHAR(10), CheckOutDay, 103) as[Ngày trả], SUM(rf.Amount) as[Tổng tiền]
	from ROOM r, ROOM_DETAILS rd, RENTFORM_DETAIL rf
	where R.RoomID =rd.RoomID and rd.DetailsID = rf.DetailsID and month(CheckOutDay)=@month and year(CheckOutDay)=@year
	group by r.RoomName, CheckInDay,CheckOutDay
	ORDER BY R.RoomName	
EXEC sp_ThongkeDoanhThuThang '6','2012'
---------Cái này không chạy------------
SELECT MAX( case ISNUMERIC(SUBSTRING(DetailsID,3,LEN(DetailsID)))WHEN 0 then 0 else SUBSTRING(DetailsID,3,LEN(DetailsID)) END) FROM ROOM_DETAILS
select IDENT_CURRENT ('RENTFORM_DETAIL') --- Lấy giá trị lớn nhất của biến identity
select DetailsID from dbo.ROOM_DETAILS order by DetailsID desc
------------------

create function so_phong_dat()
returns int
as
	BEGIN
		DECLARE @result int		
		set @result = (select COUNT(tinhtrang) from ROOM where TinhTrang='1')
		return @result
	END
--------------------------
CREATE PROC  sp_TK_tinhtrangPhong (@day int,@month int, @year int)
AS
	BEGIN
		UPDATE ROOM
		SET TinhTrang = CASE WHEN (r.ROOMID IN (SELECT ROOMID FROM ROOM_DETAILS WHERE CONVERT(DATETIME, CONVERT(nvarchar(2),@day)+'/'+CONVERT(nvarchar(2),@month)+'/'+CONVERT(nvarchar(4),@year),103) BETWEEN CheckInDay and CheckOutDay )) THEN '1' ELSE '0' END 
		from ROOM_DETAILS d,ROOM r, ROOMTYPE rt		
		select dbo.so_phong_dat() as [Đã đặt],COUNT(RoomID)[Tổng số phòng] from ROOM
	END

EXEC sp_TK_tinhtrangPhong '10','5','2012'


--------------------Cập nhập thông tin Check In-------------------------------
create proc sp_Select_CheckInInfo
(@id char(10))
as
	select rd.DetailsID, CONVERT(VARCHAR(10), CheckInDay, 103) as [CheckInDay], CONVERT(VARCHAR(10), CheckOutDay, 103) as[CheckOutDay], AmountRoom, NguoiLon,TreEm, Deposit,Amount
	from ROOM_DETAILS rd, RENTFORM_DETAIL rf
	where rd.DetailsID =rf.DetailsID and rd.DetailsID=@id

create proc sp_Update_CheckInIfo
(
	@id char(10),
	@checkInday datetime,
	@checkOutday datetime,
	@slphong int,
	@slNguoilon int,
	@slTreem int,
	@deposit char(10),
	@amount char(10)
)
as
	begin
		update ROOM_DETAILS set DetailsID=@id,CheckInDay=@checkInday,CheckOutDay=@checkOutday, AmountRoom=@slphong, NguoiLon=@slNguoilon, TreEm =@slTreem
		where DetailsID =@id
		update RENTFORM_DETAIL set Deposit = @deposit, Amount= @amount
		where DetailsID =@id		
	end

exec sp_Update_CheckInIfo ''

create proc sp_TK_theoLoaiPhong( @id char(10))
as
select RoomID,RoomName,NumberCustomer,Notes from ROOM where ROOM.RoomTypeID=@id
exec sp_TK_theoLoaiPhong '01'
------------Làm treeview--------------
create proc sp_Select_AccType
as
	select NameType,IDType from dbo.ACCTYPE
	
create proc sp_select_acountofType(@idTypet char(10))
as
		select UserName+' - ('+Name+')' as [Acc] from dbo.ACCOUNT a, dbo.ACCTYPE at, dbo.EMPLOYEE e 
	where a.IDType = at.IDType and e.EmployeeID =a.EmployeeID and  at.NameType=@idTypet

sp_select_acountofType 'admin'
---------------
create proc sp_select_EmployCoTK
as
	select e.EmployeeID as[Mã NV], Name as[Tên Nhân Viên], UserName as[Tài khoản],Pass as[Mật khẩu]
	from dbo.EMPLOYEE e,dbo.ACCOUNT a
	where e.EmployeeID=a.EmployeeID
	
create proc sp_select_EmployKocoTK
as
	select  e.EmployeeID as[Mã NV], Name as[Tên Nhân Viên],CONVERT(VARCHAR(10), BirthDay, 103) as [Ngày sinh], Mobile as[Điện thoại]
	from EMPLOYEE e
	where e.EmployeeID not in (select e.EmployeeID
								from dbo.EMPLOYEE e,dbo.ACCOUNT a
								where e.EmployeeID=a.EmployeeID)
	--------------Tạo tài khoản mới---------------
create proc sp_Insert_newAcc
(
	@id nvarchar(30),
	@pass nvarchar(30),
	@employID nvarchar(10),
	@accTypeID nvarchar(10)
)
as
	insert into ACCOUNT(UserName,Pass,EmployeeID,IDType) values (@id,@pass,@employID,@accTypeID)

--------------------------------------------------------------------
select  RoomName, [RoomStatus] = case 
from ROOM r

create proc sp_test(@a int, @b int)
as
	select b ,(CASE WHEN (a IN ( SELECT a FROM dbo.test1 t WHERE 1  BETWEEN t.a and t.b  and 3  BETWEEN t.c and t.d)) THEN '1' ELSE '0' END) AS [TRANG THAI]
	from dbo.test1
exec sp_test '2','3'

create proc sp_Room_status(@ngayDat datetime, @ngayTra datetime)
as
	select r.RoomID as [ID], r.RoomName as[Tên Phòng],
	(CASE WHEN (r.RoomID IN (SELECT RoomID FROM dbo.ROOM_DETAILS where CheckInDay BETWEEN CONVERT(DATETIME,@ngayDat,103) and CONVERT(DATETIME,@ngayTra,103) or CheckOutDay BETWEEN  CONVERT(DATETIME,@ngayDat,103) and CONVERT(DATETIME,@ngayTra,103)))THEN N'Đã đặt' ELSE N'Trống' END) as [Trạng thái]
	from dbo.ROOM_DETAILS rd, dbo.ROOM r 
	group by r.RoomID, r.RoomName
	order by r.RoomID
sp_Room_status '5/1/2012','5/15/2012'